package com.javaweb.bookstore;
import java.sql.*;

public class JdbcExample {
    // 数据库连接信息
    private static final String URL = "jdbc:mysql://localhost:3306/bookstore?serverTimezone=Asia/Shanghai&useSSL=false";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "root";

    public static void main(String[] args) {


        ResultSet resultSet = null;

        try {
            // 1. 加载驱动 (MySQL 8.0+ 自动加载，可省略)
            Class type = Class.forName("com.mysql.cj.jdbc.Driver");

            // 2. 建立连接
            Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            System.out.println("数据库连接成功！");

            // 3. 创建Statement对象
            Statement statement = connection.createStatement();

            // 4. 创建表
           createTable(statement);

            // 5. 插入数据
            insertData(statement);

            // 6. 查询数据
            queryData(statement);

            // 7. 更新数据
            updateData(statement);

            // 8. 删除数据
            deleteData(statement);

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            // 9. 关闭资源

        }
    }

    // 创建表
    private static void createTable(Statement statement) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS users (" +
                "id INT AUTO_INCREMENT PRIMARY KEY, " +
                "name VARCHAR(50) NOT NULL, " +
                "email VARCHAR(100), " +
                "age INT)";
        statement.executeUpdate(sql);
        System.out.println("表创建成功！");
    }

    // 插入数据
    private static void insertData(Statement statement) throws SQLException {
        String sql1 = "INSERT INTO users (name, email, age) VALUES ('张三', 'zhangsan@email.com', 25)";
        String sql2 = "INSERT INTO users (name, email, age) VALUES ('李四', 'lisi@email.com', 30)";

        statement.executeUpdate(sql1);
        statement.executeUpdate(sql2);
        System.out.println("数据插入成功！");
    }

    // 查询数据
    private static void queryData(Statement statement) throws SQLException {
        String sql = "SELECT * FROM users where name LIKE '%四%'";
        ResultSet resultSet = statement.executeQuery(sql);

        System.out.println("=== 查询结果 ===");
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String email = resultSet.getString("email");
            int age = resultSet.getInt("age");

            System.out.printf("ID: %d, 姓名: %s, 邮箱: %s, 年龄: %d%n", id, name, email, age);
            //构造Boo对象
        }
        resultSet.close();
    }

    // 更新数据
    private static void updateData(Statement statement) throws SQLException {
        String sql = "UPDATE users SET age = 26 WHERE name = '张三'";
        int rows = statement.executeUpdate(sql);
        System.out.println("更新了 " + rows + " 行数据");
    }

    // 删除数据
    private static void deleteData(Statement statement) throws SQLException {
        String sql = "DELETE FROM users WHERE name = '李四'";
        int rows = statement.executeUpdate(sql);
        System.out.println("删除了 " + rows + " 行数据");
    }

    // 关闭资源
    private static void closeResources(Connection conn, Statement stmt, ResultSet rs) {
        try {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
            System.out.println("资源已关闭");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}